/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package org.madmac.faixaIp;

import org.madmac.ip.IpQuery;
import org.wakeful.conn.Connection;
import org.wakeful.query.Query;

/**
 *
 * @author atila
 */
public final class FaixaIpQuery {

   public static final String TABLE = Connection.getSchema().concat(Query.DOT).concat("faixa_ip");
   public static final String ALIAS = TABLE.concat(" f");

   private FaixaIpQuery() {}

   public static String findNextIp() {
      StringBuilder builder = new StringBuilder();
      builder.append(Query.SELECT);
      builder.append("faixa_fi + 1 AS next_ip");
      builder.append(Query.FROM);
      builder.append(FaixaIpQuery.TABLE);
      builder.append(Query.WHERE);
      builder.append("id_faixa_ip = (");
      builder.append(FaixaIpQuery.selectLastFaixaIp());
      builder.append(")");
      return builder.toString();
   }

   public static String create() {
      StringBuilder builder = new StringBuilder();
      builder.append(Query.INSERT);
      builder.append(FaixaIpQuery.TABLE);
      builder.append(" (faixa_in, faixa_fi, descricao)");
      builder.append(Query.VALUES);
      builder.append("(?, ?, UPPER(?))");
		builder.append(Query.RETURNING);
		builder.append("id_faixa_ip");
      return builder.toString();
   }

   public static String update() {
      StringBuilder builder = new StringBuilder();
      builder.append(Query.UPDATE);
      builder.append(FaixaIpQuery.TABLE);
      builder.append(Query.SET);
      builder.append("descricao = ?");
      builder.append(Query.WHERE);
      builder.append("id_faixa_ip = ?");
      return builder.toString();
   }

   public static String selectLastFaixaIp() {
      StringBuilder builder = new StringBuilder();
      builder.append(Query.SELECT);
      builder.append("MAX(id_faixa_ip) AS last");
      builder.append(Query.FROM);
      builder.append(FaixaIpQuery.TABLE);
      return builder.toString();
   }

	public static String findAll() {
		StringBuilder builder = new StringBuilder();
		builder.append(Query.SELECT);
		builder.append("id_faixa_ip, faixa_in, faixa_fi, descricao");
		builder.append(Query.FROM);
      builder.append(FaixaIpQuery.TABLE);
		builder.append(Query.ORDER_BY);
		builder.append("faixa_in");
		return builder.toString();
	}

	public static String findFaixaIpLivre() {
		StringBuilder builder = new StringBuilder();
		builder.append(Query.SELECT);
		builder.append("f.id_faixa_ip, f.faixa_in, f.faixa_fi, f.descricao, COUNT(ip.ip) AS num_ip_livre");
		builder.append(Query.FROM);
      builder.append(FaixaIpQuery.ALIAS);
		builder.append(Query.LEFT_JOIN);
		builder.append(IpQuery.ALIAS);
		builder.append(Query.ON);
		builder.append("f.id_faixa_ip = ip.id_faixa_ip");
		builder.append(Query.WHERE);
      builder.append("ip.livre = true");
		builder.append(Query.GROUP_BY);
		builder.append("f.id_faixa_ip, f.faixa_in, f.faixa_fi, f.descricao");
		builder.append(Query.ORDER_BY);
		builder.append("f.faixa_in");
		return builder.toString();
	}

	public static String retrieve() {
		StringBuilder builder = new StringBuilder();
		builder.append(Query.SELECT);
		builder.append("id_faixa_ip, faixa_in, faixa_fi, descricao");
		builder.append(Query.FROM);
      builder.append(FaixaIpQuery.TABLE);
		builder.append(Query.WHERE);
		builder.append("id_faixa_ip = ?");
		return builder.toString();
	}
}
